package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import bean.Donate;
import database.Database;

public class DonateDao {
	/**
	 * 
	 * @return
	 */
	public ArrayList<Donate> getAllDonate() {
		Connection conn = null;
		try {
			conn = Database.getConnection();
			String sql = "select id,donate_name,donate_price,donate_date,donate_need from donate";
			PreparedStatement prep = conn.prepareStatement(sql);
			ResultSet rs = prep.executeQuery();

			ArrayList<Donate> donate_list = new ArrayList<Donate>();
			while (rs.next()) {
				Donate donate = new Donate();
				donate.setId(rs.getInt(1));
				donate.setDonate_name(rs.getString(2));
				donate.setDonate_price(rs.getString(3));
				donate.setDonate_date(rs.getString(4).substring(0, 10));
				donate.setDonate_need(rs.getString(5));
				donate_list.add(donate);
			}
			return donate_list;

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	public ArrayList queryConditionNews(String message, String date_start,
			String date_end) throws Exception {
		Connection conn = null;
		PreparedStatement prep;
		ArrayList<Donate> donate_list = new ArrayList<Donate>();
		try {
			conn = Database.getConnection();
			String temp_sql = "";
			if (date_start != null && !"".equals(date_start)
					&& date_end != null && !"".equals(date_end)) {
				temp_sql = "and donate_date between '" + date_start + "' and '"
						+ date_end + "'";
			}

			String sql = "select * from donate where donate_name like '%"
					+ message + "%' " + temp_sql + " order by donate_date desc";
			prep = conn.prepareStatement(sql);
			ResultSet rs = prep.executeQuery();

			while (rs.next()) {
				Donate donate = new Donate();
				donate.setId(rs.getInt(1));
				donate.setDonate_name(rs.getString(2));
				donate.setDonate_price(rs.getString(3));
				donate.setDonate_date(rs.getString(4).substring(0, 10));
				donate.setDonate_need(rs.getString(5));
				donate_list.add(donate);
			}

			prep.close();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return donate_list;
	}

	public int deleteNewsItem(String id) {
		Connection conn = null;
		try {
			conn = Database.getConnection();
			String sql = "delete from donate where id = " + id;
			PreparedStatement prep = conn.prepareStatement(sql);
			int num = prep.executeUpdate();
			prep.close();
			return num;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return 0;

	}

	/**
	 * 
	 * @param id
	 * @return
	 */
	public Donate getContent(String id) {
		Connection conn = null;
		try {
			conn = Database.getConnection();
			String sql = "select * from donate where id=" + id;

			PreparedStatement prep = conn.prepareStatement(sql);
			ResultSet rs = prep.executeQuery();

			Donate donate = new Donate();

			while (rs.next()) {
				donate.setId(rs.getInt(1));
				donate.setDonate_name(rs.getString(2));
				donate.setDonate_price(rs.getString(3));
				donate.setDonate_date(rs.getString(4));
				donate.setDonate_need(rs.getString(5));
				donate.setDonate_email(rs.getString(6));
				donate.setDonate_telephone(rs.getString(7));
				donate.setDonate_address(rs.getString(8));
				donate.setDonate_postalcode(rs.getString(9));
				donate.setDonate_alumni(rs.getString(10));
				donate.setDonate_classInformation(rs.getString(11));
				donate.setRemark(rs.getString(12));
			}

			prep.close();

			return donate;

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return null;
	}

	/**
	 * 
	 * @param id
	 * @param donate
	 * @return
	 */
	public int writeModifyInfo(String id, Donate donate) {
		Connection conn = null;
		try {
			conn = Database.getConnection();
			String sql = "update donate set donate_name=?,donate_price=?,donate_need=?,"
					+ "donate_email=?,donate_telephone=?,donate_address=?,donate_postalcode=?,"
					+ "donate_alumni=?,donate_classInformation=?,remark=? where id=?";
			PreparedStatement prep = conn.prepareStatement(sql);
			prep.setString(1, donate.getDonate_name());
			prep.setString(2, donate.getDonate_price());
			prep.setString(3, donate.getDonate_need());
			prep.setString(4, donate.getDonate_email());
			prep.setString(5, donate.getDonate_telephone());
			prep.setString(6, donate.getDonate_address());
			prep.setString(7, donate.getDonate_postalcode());
			prep.setString(8, donate.getDonate_alumni());
			prep.setString(9, donate.getDonate_classInformation());
			prep.setString(10, donate.getRemark());
			prep.setInt(11, Integer.parseInt(id));

			int num = prep.executeUpdate();
			prep.close();
			return num;

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return 0;
	}

	public int writeInsertInfo(Donate donate) {
		Connection conn = null;
		try {
			conn = Database.getConnection();
			String sql = "insert into donate(donate_name,donate_price,donate_date,"
					+ "donate_need,donate_email,donate_telephone,"
					+ "donate_address,donate_postalcode,donate_alumni,"
					+ "donate_classInformation,remark) values(?,?,?,?,?,?,?,?,?,?,?)";
			PreparedStatement prep = conn.prepareStatement(sql);
			prep.setString(1, donate.getDonate_name());
			prep.setString(2, donate.getDonate_price());
			prep.setString(3, donate.getDonate_date());
			prep.setString(4, donate.getDonate_need());
			prep.setString(5, donate.getDonate_email());
			prep.setString(6, donate.getDonate_telephone());
			prep.setString(7, donate.getDonate_address());
			prep.setString(8, donate.getDonate_postalcode());
			prep.setString(9, donate.getDonate_alumni());
			prep.setString(10, donate.getDonate_classInformation());
			prep.setString(11, donate.getRemark());

			int num = prep.executeUpdate();
			prep.close();
			return num;

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return 0;
	}
}
